//    Openbravo POS is a point of sales application designed for touch screens.
//    Copyright (S) 2007-2009 Openbravo, S.L.
//    http://www.openbravo.com/product/pos
//
//    This file is part of Openbravo POS.
//
//    Openbravo POS is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    Openbravo POS is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with Openbravo POS.  If not, see <http://www.gnu.org/licenses/>.

package com.openbravo.data.loader;


import com.openbravo.data.loader.dialect.AbstractDBDialect;
import com.openbravo.data.loader.sentence.*;
import com.openbravo.data.loader.serialize.serializer.SerializerRead;
import com.openbravo.data.loader.serialize.serializer.SerializerWrite;
import com.openbravo.data.loader.serialize.serializer.read.SerializerReadDatas;
import com.openbravo.data.loader.serialize.serializer.read.SerializerReadInteger;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteString;
import e.odbo.data.dsl.query.QBFParameter;
import e.odbo.data.dsl.query.QBFParameters;
import com.openbravo.data.loader.serialize.DataWriteUtils;
import com.openbravo.data.loader.sqlbuilder.QBFBuilder;
import e.odbo.data.dao.table.AbstractTableSentenceBuilder;
import e.odbo.data.dao.table.TableDefinition;
import e.odbo.data.dao.I_ParentNode;
import e.odbo.data.dsl.operation.QueryOperation;
import e.odbo.data.sample.security.NoScuritySupportManager;
import e.odbo.data.security.DBSecurityFilterBuilder;
import e.odbo.data.sample.security.I_SecurityFilter;
import e.odbo.data.dsl.Select;


import java.util.Date;
import java.util.HashMap;

public class TableDBSentenceBuilder extends AbstractTableSentenceBuilder {
    HashMap<TableDefinition,DBSecurityFilterBuilder> filterBuilderCache=new HashMap<>();

    public TableDBSentenceBuilder(DBSession m_s){
        super(m_s,new NoScuritySupportManager());
    }

    public TableDBSentenceBuilder(DBSession m_s,I_SecurityFilter securityFilter) {
        super(m_s,securityFilter);
    }

    @Override
    public SentenceList getListSentence(TableDefinition tableDefinition) {
        return getListSentence(tableDefinition,tableDefinition.getSerializerReadBasic());
    }

    @Override
    public SentenceList getListSentence(TableDefinition tableDefinition, SerializerRead sr) {
        return new PreparedSentence(m_s, getListSQL(tableDefinition), null, sr);

    }

    public  SentenceList getListSentence(TableDefinition tableDefinition,SerializerRead sr,int[] fieldIndexs){
        return new PreparedSentence(m_s, getListSQL(tableDefinition,fieldIndexs), null, sr);
    }

    public  SentenceList getListWithTimeSentence(TableDefinition tableDefinition,SerializerRead sr, Date start, Date end){
        return new StaticSentence(m_s, getListSQL(tableDefinition,start,end), null, sr);
    }

    @Override
    //调用此为前应时行有效性检查
    public SentenceList getListByParentSentence(TableDefinition tableDefinition, I_ParentNode parent,Class realParentClass,SerializerRead sr) {
        return getListByParentSentence(tableDefinition,parent,realParentClass,sr,null);
    }

    @Override
    public SentenceList getLisBySelectSentence(Select select, SerializerRead sr) {
        return new StaticSentence(m_s, m_s.getDialect().getSQL(select), null, sr);
    }

    //list QueryOperation
    @Override
    public SentenceList getListSentence(TableDefinition tableDefinition,QueryOperation queryOperation){
        return getListSentence(tableDefinition,null,queryOperation);
    }
    @Override
    public SentenceList getListSentence(TableDefinition tableDefinition,SerializerRead sr,QueryOperation queryOperation){
        return getListSentence(tableDefinition,null,tableDefinition.getAllFields(),queryOperation);
    }
    @Override
    public SentenceList getListSentence(TableDefinition tableDefinition,SerializerRead sr,int[] fieldIndexs,QueryOperation queryOperation){
        return new PreparedSentence(m_s, getListSQL(tableDefinition,fieldIndexs,queryOperation,null), null, sr);
    }
    @Override
    public SentenceList getListWithTimeSentence(TableDefinition tableDefinition,SerializerRead sr, Date start, Date end,QueryOperation queryOperation){
        return new StaticSentence(m_s, getListSQL(tableDefinition,start,end,queryOperation), null, sr);
    }
    @Override
    public SentenceList getListByParentSentence(TableDefinition tableDefinition,I_ParentNode parent,Class realParentClass,SerializerRead sr,QueryOperation queryOperation){
        return new PreparedSentence(m_s, getListByParentSQL(tableDefinition,realParentClass,queryOperation), SerializerWriteString.INSTANCE, sr);
    }



    public  String getListSQL(TableDefinition tableDefinition) {
            return getListSQL(tableDefinition,tableDefinition.getAllFields());
        }

        public  String getListSQL(TableDefinition tableDefinition,String alise) {

            return getListSQL(tableDefinition,tableDefinition.getAllFields(),null,alise);
        }

        public String getListSQL(TableDefinition tableDefinition,int[] fieldIndexs){

            return getListSQL(tableDefinition,fieldIndexs,null,null);
        }


        public  String getListSQL(TableDefinition tableDefinition, QueryOperation queryOperation){

            return getListSQL(tableDefinition,tableDefinition.getAllFields(),queryOperation,null);
        }

        //生成排序，分页等操作
        public  String getListSQL(TableDefinition tableDefinition, int[] fieldIndexs,QueryOperation queryOperation,String alise){
            StringBuilder sent=new StringBuilder();
            sent.append(BaseQuerySqlBuilder.getListSQL(tableDefinition,fieldIndexs,alise));

            sent.append(andFilterSQL(tableDefinition));

            if(queryOperation!=null) {
                sent.append(((AbstractDBDialect) m_s.getDialect()).getSQL(queryOperation));
                if(queryOperation.orders.isEmpty())
                    sent.append(tableDefinition.getDefaultListOrder(alise));
            }
            else
                sent.append(tableDefinition.getDefaultListOrder(alise));

            return sent.toString();
        }

    public String getListByParentSQL(TableDefinition tableDefinition,Class realParentClass,QueryOperation queryOperation){
        StringBuilder sent=new StringBuilder(BaseQuerySqlBuilder.getListSQL(tableDefinition));
        sent.append(" WHERE " + tableDefinition.getParentFieldName(realParentClass) + "= ?");

        //有父类时，已有限定，如 按单号加载单内
        sent.append(andFilterSQL(tableDefinition));

        if(queryOperation!=null) {
            sent.append(((AbstractDBDialect) m_s.getDialect()).getSQL(queryOperation));
            if(queryOperation.orders.isEmpty())
                sent.append(tableDefinition.getDefaultListOrder(null));
        }else
            sent.append(tableDefinition.getDefaultListOrder(null));


        return  sent.toString();
    }

    @Override
    public SentenceFind getFindSentence(TableDefinition tableDefinition) {
        return getFindSentence(tableDefinition,null);
    }

    @Override
    public SentenceFind getFindSentence(TableDefinition tableDefinition, SerializerRead sr) {
        return getFindSentence(tableDefinition, sr,null);
    }

    @Override
    public SentenceFind getFindSentence(TableDefinition tableDefinition,SerializerRead sr, SerializerWrite sw) {
        return new PreparedSentence(m_s, getFindSQL(tableDefinition), sw, sr);
    }

    public  SentenceFind getFindSentence(TableDefinition tableDefinition,SerializerRead sr, SerializerWrite sw,int[] fieldIndexs){
        return new PreparedSentence(m_s, getFindSQL(tableDefinition,fieldIndexs), sw, sr);
    }


    public String getFindSQL(TableDefinition tableDefinition) {
        return getFindSQL(tableDefinition,tableDefinition.getAllFields());
    }


    public String getFindSQL(TableDefinition tableDefinition,int[] fieldindx) {
        StringBuilder sent = new StringBuilder();
        sent.append("select ");

        for (int i = 0; i < fieldindx.length; i++) {
            if (i > 0) {
                sent.append(", ");
            }
            sent.append(tableDefinition.fieldname[fieldindx[i]]);
        }

        sent.append(" from ");
        sent.append(tableDefinition.tablename);

        for (int i = 0; i < tableDefinition.idinx.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(tableDefinition.fieldname[tableDefinition.idinx[i]]);
            sent.append(" = ?");
        }

        return sent.toString();
    }

    @Override
    public SentenceExec getDeleteSentence(TableDefinition tableDefinition) {
        return getDeleteSentence(tableDefinition,tableDefinition.getSerializerDeleteBasic());
    }

    @Override
    public SentenceExec getDeleteSentence(TableDefinition tableDefinition,SerializerWrite sw) {
        return new PreparedSentence(m_s, getDeleteSQL(tableDefinition), sw, null);
    }

    //不加数据权限，因id有唯一性，是最好的数据规则，用户删除权限，最好在上层判断
    public String getDeleteSQL(TableDefinition tableDefinition) {

        StringBuilder sent = new StringBuilder();
        sent.append("delete from ");
        sent.append(tableDefinition.tablename);

        for (int i = 0; i < tableDefinition.idinx.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(tableDefinition.fieldname[tableDefinition.idinx[i]]);
            sent.append(" = ?");
        }

        return sent.toString();
    }

    //加入权限
    public String getDeleteSQL(TableDefinition tableDefinition,String[] fields) {

        StringBuilder sent = new StringBuilder();
        sent.append("delete from ");
        sent.append(tableDefinition.tablename);

        for (int i = 0; i < fields.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(fields[i]);
            sent.append(" = ?");
        }

        if(fields.length>0)
            sent.append(andFilterSQL(tableDefinition));
        else
            sent.append(whereFilterSQL(tableDefinition));
        return sent.toString();
    }


    @Override
    public SentenceExec getClearSentence(TableDefinition tableDefinition) {
        return new StaticSentence(m_s, getClearSQL(tableDefinition), null, null);
    }

    //加入数据权限
    public String getClearSQL(TableDefinition tableDefinition) {
        return "delete from " + tableDefinition.tablename+whereFilterSQL(tableDefinition);
    }


    @Override
    public SentenceExec getInsertSentence(TableDefinition tableDefinition) {
        return getInsertSentence(tableDefinition,tableDefinition.getAllFields());
    }

    @Override
    public SentenceExec getInsertSentence(TableDefinition tableDefinition,int[] fieldindx) {
        return new PreparedSentence(m_s, getInsertSQL(tableDefinition,fieldindx), tableDefinition.getSerializerInsertBasic(fieldindx), null);
    }

    @Override
    public SentenceExec getInsertSentence(TableDefinition tableDefinition,SerializerWrite objWrite) {
        return new PreparedSentence(m_s, getInsertSQL(tableDefinition,tableDefinition.getAllFields()), objWrite, null);
    }

    public String getInsertSQL(TableDefinition tableDefinition,int[] fieldindx) {

        StringBuilder sent = new StringBuilder();
        StringBuilder values = new StringBuilder();

        sent.append("insert into ");
        sent.append(tableDefinition.tablename);
        sent.append(" (");

        for (int i = 0; i < fieldindx.length; i++) {
            if (i > 0) {
                sent.append(", ");
                values.append(", ");
            }
            sent.append(tableDefinition.fieldname[fieldindx[i]]);
            values.append("?");
        }

        sent.append(") values (");
        sent.append(values.toString());
        sent.append(")");

        return sent.toString();
    }


    @Override
    public SentenceFind getFindByFieldIndexSentence(TableDefinition tableDefinition,int index) {
        return new StaticSentence(m_s, getFindSQL(tableDefinition,new int[]{index}), tableDefinition.getSerializerExistBasic(), new SerializerReadDatas(tableDefinition.fielddata[index]));
    }

    @Override
    public SentenceExec getCountSentence(TableDefinition tableDefinition) {
        return new StaticSentence(m_s, getCountSql(tableDefinition), null, SerializerReadInteger.INSTANCE);
    }

    @Override
    public SentenceFind getCountSentence(TableDefinition tableDefinition,QBFParameters filter) {
        return new StaticSentence(m_s, getCountSql(tableDefinition), null, SerializerReadInteger.INSTANCE);
    }

    //加入数据权限
    public  String getCountSql(TableDefinition tableDefinition) {
        return BaseQuerySqlBuilder.getCountSql(tableDefinition)+andFilterSQL(tableDefinition);
    }

    @Override
    public SentenceFind getExistSentence(TableDefinition tableDefinition,SerializerWrite rowWriter) {
        return new StaticSentence(m_s, getExistSql(tableDefinition), rowWriter, SerializerReadInteger.INSTANCE);
    }

    public String getExistSql(TableDefinition tableDefinition) {
        StringBuilder sent = new StringBuilder();
        sent.append(BaseQuerySqlBuilder.getCountSql(tableDefinition));
        for (int i = 0; i < tableDefinition.idinx.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(tableDefinition.fieldname[tableDefinition.idinx[i]]);
            sent.append(" = ?");
        }
        return sent.toString();
    }

    @Override
    public SentenceFind getExistSentence(TableDefinition tableDefinition,String[] fields) {
        return new StaticSentence(m_s, getExistSql(tableDefinition,fields), tableDefinition.getSerializerWriteBase(fields), SerializerReadInteger.INSTANCE);
    }

    //加入数据权限
    public String getExistSql(TableDefinition tableDefinition,String[] fields) {
        StringBuilder sent = new StringBuilder();
        sent.append(BaseQuerySqlBuilder.getCountSql(tableDefinition));
        for (int i = 0; i < fields.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(fields[i]);
            sent.append(" = ?");
        }
        if(fields.length>0)
            sent.append(andFilterSQL(tableDefinition));
        else
            sent.append(whereFilterSQL(tableDefinition));
        return sent.toString();
    }

    @Override
    public SentenceExec getUpdateSentence(TableDefinition tableDefinition) {
        return getUpdateSentence(tableDefinition,tableDefinition.getAllFields());
    }

    @Override
    public SentenceExec getUpdateSentence(TableDefinition tableDefinition,int[] fieldindx) {
        return new PreparedSentence(m_s, getUpdateSQL(tableDefinition,fieldindx), tableDefinition.getSerializerUpdateBasic(fieldindx), null);
    }


    public SentenceExec getUpdateSentence(TableDefinition tableDefinition,int[] fieldindx, SerializerWrite rowWriter) {
        return new PreparedSentence(m_s, getUpdateSQL(tableDefinition,fieldindx), rowWriter, null);
    }

    @Override
    public SentenceExec getUpdateSentence(TableDefinition tableDefinition,SerializerWrite rowWriter) {
        return new PreparedSentence(m_s, getUpdateSQL(tableDefinition,tableDefinition.getAllFields()), rowWriter, null);
    }

    public String getUpdateSQL(TableDefinition tableDefinition,int[] fieldindx) {
        StringBuilder sent = new StringBuilder();
        sent.append("update ");
        sent.append(tableDefinition.tablename);
        sent.append(" set ");
        int j = 0;
        for (int i = 0; i < fieldindx.length; i++) {
            if (tableDefinition.updateIgnoreField == null || !tableDefinition.updateIgnoreField[i]) {//注在使用普通模式时应设过滤条件为null;
                if (j > 0) {
                    sent.append(", ");
                }
                sent.append(tableDefinition.fieldname[fieldindx[i]]);
                sent.append(" = ?");
                j++;
            }
        }
        for (int i = 0; i < tableDefinition.idinx.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(tableDefinition.fieldname[tableDefinition.idinx[i]]);
            sent.append(" = ?");
        }
        return sent.toString();
    }

    public String getUpdateWhoutIgnoreSQL(TableDefinition tableDefinition,int[] fieldindx) {
        StringBuilder sent = new StringBuilder();
        sent.append("update ");
        sent.append(tableDefinition.tablename);
        sent.append(" set ");
        int j = 0;
        for (int i = 0; i < fieldindx.length; i++) {
            if (j > 0) {
                sent.append(", ");
            }
            sent.append(tableDefinition.fieldname[fieldindx[i]]);
            sent.append(" = ?");
            j++;
        }
        for (int i = 0; i < tableDefinition.idinx.length; i++) {
            sent.append((i == 0) ? " where " : " and ");
            sent.append(tableDefinition.fieldname[tableDefinition.idinx[i]]);
            sent.append(" = ?");
        }
        return sent.toString();
    }


    public String getSelectQBFSQL(TableDefinition tableDefinition) {
        return getSelectQBFSQL(tableDefinition,tableDefinition.getAllFields());
    }

    public String getSelectQBFSQL(TableDefinition tableDefinition,int[] fieldindx) {
        return getSelectQBFSQL(tableDefinition,fieldindx,null);
    }

    //加入数据权限
    public String getSelectQBFSQL(TableDefinition tableDefinition,int[] fieldindx,QueryOperation queryOperation) {
        StringBuilder sent = new StringBuilder();
        sent.append("select ");

        for (int i = 0; i < fieldindx.length; i++) {
            if (i > 0) {
                sent.append(", ");
            }
            sent.append(tableDefinition.fieldname[fieldindx[i]]);
        }
        sent.append(" from ");
        sent.append(tableDefinition.tablename);
        sent.append(" WHERE ?(QBF_FILTER) ");

        sent.append(andFilterSQL(tableDefinition));

        if(queryOperation==null) {
            sent.append(tableDefinition.getDefaultListOrder(null));
            return sent.toString();
        }

        if(queryOperation!=null) {
          sent.append(((AbstractDBDialect) m_s.getDialect()).getSQL(queryOperation));
          if(queryOperation.orders.isEmpty())
              sent.append(tableDefinition.getDefaultListOrder(null));
         }else
            sent.append(tableDefinition.getDefaultListOrder(null));


        return sent.toString();
    }

    public String getListSQL(TableDefinition tableDefinition,Date start, Date end) {
        return getListSQL(tableDefinition,start,end,null);
    }

    //加入数据权限,高级操作
    public String getListSQL(TableDefinition tableDefinition,Date start, Date end,QueryOperation queryOperation) {
        String sql = BaseQuerySqlBuilder.getListSQL(tableDefinition,tableDefinition.getAllFields());
        String dateField = tableDefinition.getDateFieldName();
        if (dateField != null) {
            sql += " where ";
            if (start != null && end != null) {
                sql += tableDefinition.getDateFieldName() + ">=" + DataWriteUtils.getSQLValue(start);
                sql += " and " + tableDefinition.getDateFieldName() + "<=" + DataWriteUtils.getSQLValue(end);
            } else if (start != null) {
                sql += tableDefinition.getDateFieldName() + ">=" + DataWriteUtils.getSQLValue(start);
            } else if (end != null) {
                sql += tableDefinition.getDateFieldName() + "<=" + DataWriteUtils.getSQLValue(end);
            } else
                sql += "(1=1)";
            sql+=andFilterSQL(tableDefinition);
        }else
            sql +=whereFilterSQL(tableDefinition);

        if(queryOperation==null) {
            sql+=tableDefinition.getDefaultListOrder(null);
            return sql;
        }

        StringBuilder sent=new StringBuilder(sql);
        //高级queryOperation

        if(queryOperation!=null) {
            sent.append(((AbstractDBDialect) m_s.getDialect()).getSQL( queryOperation));
            if(queryOperation.orders.isEmpty())
                sent.append(tableDefinition.getDefaultListOrder(null));
        }else
            sent.append(tableDefinition.getDefaultListOrder(null));

        return sent.toString();
    }



    public String getDeleteQBFSQL(TableDefinition tableDefinition) {
        return getDeleteQBFSQL(tableDefinition,null);
    }

    //加入数据权限,高级操作
    public String getDeleteQBFSQL(TableDefinition tableDefinition,QueryOperation queryOperation) {
        StringBuilder sent = new StringBuilder();
        sent.append("delete from ");
        sent.append(tableDefinition.tablename);
        sent.append(" WHERE ?(QBF_FILTER) ");

        sent.append(andFilterSQL(tableDefinition));

        return sent.toString();

    }

    //加入数据权限
    public String getCountQBFSQL(TableDefinition tableDefinition) {
        return BaseQuerySqlBuilder.getCountSql(tableDefinition) + " WHERE ?(QBF_FILTER) "+andFilterSQL(tableDefinition);
    }

    @Override
    public SentenceList getQuerySentence(TableDefinition tableDefinition, QBFParameters filter,SerializerRead sr) {
        return getQuerySentence(tableDefinition,filter,sr,null);
    }


    @Override
    public SentenceList getQuerySentence(TableDefinition tableDefinition, QBFParameters filter, SerializerRead sr, QueryOperation queryOperation) {
        return new StaticSentence(m_s, new QBFBuilder(getSelectQBFSQL(tableDefinition,tableDefinition.getAllFields(),queryOperation)), null, sr);
    }

    @Override
    public SentenceList getQuerySentence(TableDefinition tableDefinition, int[] fieldIndexs, QBFParameters filter, SerializerRead sr, QueryOperation queryOperation) {
        return new StaticSentence(m_s, new QBFBuilder(getSelectQBFSQL(tableDefinition,fieldIndexs,queryOperation)), null, sr);
    }

    @Override
    public SentenceList getQuerySentence(TableDefinition tableDefinition, int[] fieldIndexs, QBFParameters filter, SerializerRead sr) {
        return new StaticSentence(m_s, new QBFBuilder(getSelectQBFSQL(tableDefinition,fieldIndexs)), null, sr);
    }

    @Override
    public SentenceExec getDeleteSentence(TableDefinition tableDefinition, QBFParameters filter) {
        return new StaticSentence(m_s, new QBFBuilder(getDeleteQBFSQL(tableDefinition)), null, SerializerReadInteger.INSTANCE);
    }


    @Override
    public SentenceFind getCountSentence(TableDefinition tableDefinition, QBFParameter filter) {
       return new StaticSentence(m_s, new QBFBuilder(getCountQBFSQL(tableDefinition)), null, SerializerReadInteger.INSTANCE);
    }



    //权限相关
    public String andFilterSQL(TableDefinition tableDefinition) {
        return andFilterSQL(tableDefinition,null);
    }

    public String andFilterSQL(TableDefinition tableDefinition, String alise) {
        DBSecurityFilterBuilder builder=getSecurityFilterBuilder(tableDefinition);
        return builder.andSQL(securityFilter, alise);
    }

    public String whereFilterSQL(TableDefinition tableDefinition) {
        return whereFilterSQL(tableDefinition,null);
    }

    public String whereFilterSQL(TableDefinition tableDefinition, String alise) {
        DBSecurityFilterBuilder builder=getSecurityFilterBuilder(tableDefinition);
        return builder.whereSQL(securityFilter, alise);
    }

    private  DBSecurityFilterBuilder getSecurityFilterBuilder(TableDefinition tableDefinition){
        DBSecurityFilterBuilder builder=filterBuilderCache.get(tableDefinition);
        if(builder==null){
            builder=new DBSecurityFilterBuilder(tableDefinition);
            filterBuilderCache.put(tableDefinition,builder);
        }
        return builder;
    }

    public static String getBaseListSQL(TableDefinition tableDefinition){
        return BaseQuerySqlBuilder.getListSQL(tableDefinition);
    }

    public static String getBaseListSQL(TableDefinition tableDefinition, int[] fieldindx){
        return BaseQuerySqlBuilder.getListSQL(tableDefinition, fieldindx);
    }

    public static String getBaseListSQL(TableDefinition tableDefinition, String alise){
        return BaseQuerySqlBuilder.getListSQL(tableDefinition,tableDefinition.getAllFields(),alise);
    }

    public static String getBaseListSQL(TableDefinition tableDefinition, int[] fieldindx,String alise){
        return BaseQuerySqlBuilder.getListSQL(tableDefinition, fieldindx, alise);
    }


    public static class BaseQuerySqlBuilder{
        public static String getListSQL(TableDefinition tableDefinition){
            return getListSQL(tableDefinition,tableDefinition.getAllFields());
        }

        public static String getListSQL(TableDefinition tableDefinition, int[] fieldindx) {
            return getListSQL(tableDefinition,fieldindx,null);
        }

        public static String getListSQL(TableDefinition tableDefinition, int[] fieldindx,String alise) {

            StringBuilder sent = new StringBuilder();
            sent.append("select ");

            for (int i = 0; i < fieldindx.length; i++) {
                if (i > 0) {
                    sent.append(", ");
                }
                if(alise!=null&&!alise.isEmpty())
                    sent.append(alise + "." + tableDefinition.fieldname[fieldindx[i]]);
                else
                    sent.append( tableDefinition.fieldname[fieldindx[i]]);
            }

            sent.append(" from ");
            if(alise!=null&&!alise.isEmpty())
                sent.append(tableDefinition.tablename + " " + alise + " ");
            else
                sent.append(tableDefinition.tablename);
            return sent.toString();
        }

        public static String getCountSql(TableDefinition tableDefinition) {
            return "SELECT COUNT(*) FROM " + tableDefinition.tablename;
        }

        public static String getInsert(String table,String[] columns){
            StringBuilder sent = new StringBuilder();
            StringBuilder values = new StringBuilder();

            sent.append("insert into ");
            sent.append(table);
            sent.append(" (");

            for (int i = 0; i < columns.length; i++) {
                if (i > 0) {
                    sent.append(", ");
                    values.append(", ");
                }
                sent.append(columns[i]);
                values.append("?");
            }

            sent.append(") values (");
            sent.append(values.toString());
            sent.append(")");

            return sent.toString();
        }

        public static String getDelete(String table,String[] columns){
            StringBuilder sent = new StringBuilder();
            sent.append("delete from ");
            sent.append(table);

            for (int i = 0; i < columns.length; i++) {
                sent.append((i == 0) ? " where " : " and ");
                sent.append(columns[i]);
                sent.append(" = ?");
            }

            return sent.toString();
        }
    }
}
